package org.feluma.sgci.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.feluma.sgci.entidade.Curso;
import org.feluma.sgci.entidade.Cadastro;
import org.feluma.sgci.util.Conexao;
import org.feluma.sgci.util.Excecao;
import org.feluma.sgci.util.Testes;

public class CadastroDAO {

	public CadastroDAO() throws Excecao{}
	
	public void cadastrar(Cadastro cadastro) throws Excecao{
		Connection conexao = Conexao.getConexaoMssql();
		PreparedStatement ps = null;
		
		String sql = " insert into cadastro (codCur,codEsc,cpfCan,nomCan,emaCan,telComCan,telResCan,telCelCan," +
				                            " logCan,numCan,cplCan,baiCan,cidCan,ufCan,cepCan,obsCan,datCadCan)"+
                                            " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,getdate())";
		
		try {
			ps = conexao.prepareStatement(sql);
			ps.setInt(1, cadastro.getCurso().getCodigoCurso());
			ps.setInt(2, cadastro.getCurso().getCodigoEscola());
			ps.setString(3, cadastro.getCpf());
			ps.setString(4, cadastro.getNome());
			ps.setString(5, cadastro.getEmail());
			ps.setString(6, cadastro.getTelefoneComercial());
			ps.setString(7,cadastro.getTelefoneResidencial());
			ps.setString(8,cadastro.getTelefoneCelular());
			ps.setString(9, cadastro.getEndereco());
			ps.setString(10, cadastro.getNumero());
			ps.setString(11, cadastro.getComplemento());
			ps.setString(12, cadastro.getBairro());
			ps.setString(13, cadastro.getCidade());
			ps.setString(14, cadastro.getEstado());
			ps.setString(15, cadastro.getCep());
			ps.setString(16, cadastro.getObservacao());
			ps.execute();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Conexao.closeConnection(conexao, ps);
		}		
	}
	
	public ArrayList<Cadastro> listar(Cadastro cadastro) throws Excecao{
		Connection conexao = Conexao.getConexaoMssql();
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<Cadastro> lista = new ArrayList<Cadastro>();
		String separador = "";
		String sql = " select * from cadastro ";
		if(cadastro != null){
			
			sql = " select * from cadastro  where "; 
		
			if(cadastro.getCodigo() > 0){
				sql += separador + " codCan = " + cadastro.getCodigo();
				separador = " and ";
			}
			
			if(cadastro.getCurso().getCodigoCurso() > 0){
				sql += separador + " codcur= " + cadastro.getCurso().getCodigoCurso();
				separador = " and ";
			}
			
			if(cadastro.getCurso().getCodigoEscola() > 0){
				sql += separador + " codEsc = " + cadastro.getCurso().getCodigoEscola();
				separador = " and ";
			}
			
			if(! Testes.isNull(cadastro.getCpf())){
				sql += separador + " cpfCan = '" + cadastro.getCpf() + "'";
				separador = " and ";
			}

			if(! Testes.isNull(cadastro.getNome())){
				sql += separador + " nomCan like '%" + cadastro.getNome() +"%'";
				separador = " and ";
			}

			if(! Testes.isNull(cadastro.getEmail())){
				sql += separador + " emaCan like '%" + cadastro.getEmail() +"%'";
				separador = " and ";
			}			
		}
		
		sql += " order by nomCan";
		try {
			ps = conexao.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()){
				CursoDAO curDAO = new CursoDAO();
				lista.add(new Cadastro(rs.getInt("codCan"),curDAO.procurar(rs.getInt("codCur")),rs.getString("cpfCan"),rs.getString("nomCan"),
						rs.getString("emaCan"),rs.getString("telComCan"),rs.getString("telResCan"),rs.getString("telCelCan"),rs.getString("logCan"),
						rs.getString("numCan"),rs.getString("cplCan"),rs.getString("baiCan"),rs.getString("cidCan"),rs.getString("ufCan"),
						rs.getString("cepCan"),rs.getString("obsCan"),rs.getDate("datCAdCan")));
			}
			
			
		} catch (Exception e) {
			return lista;
		}finally{
			Conexao.closeConnection(conexao, ps, rs);
		}
			
		return lista;
	}
	
	public ArrayList<Cadastro> listarCandidato(int codEscola, int codCurso) throws Excecao{
		Connection conexao = Conexao.getConexaoMssql();
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<Cadastro> listaCadastro = new ArrayList<Cadastro>();
		String sql = " select distinct codCan =max(codCan), cad.codEsc, cad.codCur, cpfCan, nomCan, emaCan, TelComCan, telResCan, telCelCan, " +
				     " logCan, numCan, cplCan, baiCan, cidCan, ufCan, cepCan, obsCan = substring(obsCan,1,3000), cur.nomCur,datCadCan = max(datCadCan) " +
				     " from cadastro cad join vwcurso cur on (cad.codEsc = cur.codEsc and " + 
                     "                                        cad.codCur = cur.codcur) " +
                     " where cad.codEsc =? and cad.codCur =? " +
                     " group by cad.codEsc, cad.codCur, cpfCan, nomCan, emaCan, TelComCan, telResCan, " +
                     " telCelCan,  logCan, numCan, cplCan, baiCan, cidCan, ufCan, cepCan,substring(obsCan,1,3000),cur.nomcur" +
                     " order by cad.nomCan";		
		try {
			ps = conexao.prepareStatement(sql);
			ps.setInt(1, codEscola);
			ps.setInt(2, codCurso);
			rs = ps.executeQuery();
			while (rs.next()){
				listaCadastro.add(new Cadastro(rs.getInt("codCan"),
						                       new Curso(rs.getInt("codEsc"),rs.getInt("codCur"),rs.getString("nomCur")),
						                       rs.getString("cpfCan"),rs.getString("nomCan"),rs.getString("emaCan"),
						                       rs.getString("telComCan"),rs.getString("telResCan"),rs.getString("telCelCan"),
						                       rs.getString("logCan"),rs.getString("numCan"),rs.getString("cplCan"),rs.getString("baiCan"),
						                       rs.getString("cidCan"),rs.getString("ufCan"),rs.getString("cepCan"),rs.getString("obsCan"), 
						                       rs.getDate("datCadCan")));				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listaCadastro;
	}
	
	public ArrayList<Cadastro> listarCandidatoExcel(int codEscola, int codCurso) throws Excecao{
		Connection conexao = Conexao.getConexaoMssql();
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<Cadastro> listaCadastro = new ArrayList<Cadastro>();
		String sql = " select distinct cpfcan,nomCan,emaCan,telcomcan,telrescan,telcelcan,logcan," +
			         " numCan,cplCan,baiCan,cidCan,ufCan,cepCan, cur.codCur, cur.nomCur,cur.codesc, datCadCan= max(datCadCan) " +
				     " from cadastro cad join vwcurso cur on (cad.codEsc = cur.codEsc and " + 
                     "                                        cad.codCur = cur.codcur) " +
                     " where cad.codEsc =? and cad.codCur =? " +
                     " group by cpfcan,nomCan,emaCan,telcomcan,telrescan,telcelcan,logcan, numCan,cplCan,baiCan, " +
                     " cidCan,ufCan,cepCan,cur.codCur, cur.nomCur,cur.codesc" +
                     " order by cad.nomCan";		
		try {
			ps = conexao.prepareStatement(sql);
			ps.setInt(1, codEscola);
			ps.setInt(2, codCurso);
			rs = ps.executeQuery();
			while (rs.next()){
				listaCadastro.add(new Cadastro(
						                       new Curso(rs.getInt("codEsc"),rs.getInt("codCur"),rs.getString("nomCur")),
						                       rs.getString("cpfCan"),rs.getString("nomCan"),rs.getString("emaCan"),
						                       rs.getString("telComCan"),rs.getString("telResCan"),rs.getString("telCelCan"),
						                       rs.getString("logCan"),rs.getString("numCan"),rs.getString("cplCan"),rs.getString("baiCan"),
						                       rs.getString("cidCan"),rs.getString("ufCan"),rs.getString("cepCan"),rs.getDate("datCadCan")));				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listaCadastro;
	}

	public Cadastro procurar(int codigo) throws Excecao {
		Connection conexao = Conexao.getConexaoMssql();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Cadastro cadastro = new Cadastro();
		ArrayList<Cadastro> listaCadastro = new ArrayList<Cadastro>();
		String sql = " select distinct codCan, cad.codEsc, cad.codCur, cpfCan, nomCan, emaCan, TelComCan, telResCan, telCelCan, " +
				     " logCan, numCan, cplCan, baiCan, cidCan, ufCan, cepCan, datCadCan, obsCan = substring(obsCan,1,3000), cur.nomCur " +
				     " from cadastro cad join vwcurso cur on (cad.codEsc = cur.codEsc and " + 
                     "                                        cad.codCur = cur.codcur) " +
                     " where cad.codCan =? ";		
		try {
			ps = conexao.prepareStatement(sql);
			ps.setInt(1, codigo);
			rs = ps.executeQuery();
			while (rs.next()){
				cadastro = new Cadastro(new Curso(rs.getInt("codEsc"),rs.getInt("codCur"),rs.getString("nomCur")),
						                rs.getString("cpfCan"),rs.getString("nomCan"),rs.getString("emaCan"),
						                rs.getString("telComCan"),rs.getString("telResCan"),rs.getString("telCelCan"),
						                rs.getString("logCan"),rs.getString("numCan"),rs.getString("cplCan"),rs.getString("baiCan"),
						                rs.getString("cidCan"),rs.getString("ufCan"),rs.getString("cepCan"));				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return cadastro;
	}

	public ArrayList<Cadastro> pesquisar(String nome, int codCurso) throws Excecao {
		Cadastro cadastro = new Cadastro();
		Curso curso = new Curso();
		curso.setCodigoCurso(codCurso);
		cadastro.setCurso(curso);
		cadastro.setNome(nome);
		ArrayList<Cadastro>  listaCandidato = new ArrayList<Cadastro>();
		try {
			listaCandidato = this.listar(cadastro);
		} catch (Excecao e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return listaCandidato;
	}

}
